﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System.Data.SqlClient;
using System.Web.UI.WebControls;
using OSISFunction;
using AjaxControlToolkit;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.OleDb;
using OpenSIS.Installation;
using MySql.Data.MySqlClient;
using System;
using System.Data;
using OpenSIS.OpenSISWCFServiceReference;
using System.Web;
using System.Globalization;


public partial class Installation_Installation : System.Web.UI.Page
{
    Installation Install = new Installation();

    public string ConnStr;

    protected void Page_Load(object sender, EventArgs e)
    {
        btnExit.Attributes.Add("onclick", "window.close();");
        btnExitAftrinstl.Attributes.Add("onclick", "window.close();");

        //OpenSISWCFServiceInstallationClient INCL = new OpenSISWCFServiceInstallationClient();

        //INCL.InsertConnectionSettings("Persist Security Info=False;Provider=SQLOLEDB;Persist Security Info=False;User ID=osisnew;pwd=osisnew2009;Data Source=acdir;Database=osis_new;", "MSSQLServer");

      //   INCL = new OpenSISWCFServiceInstallationClient();

        //string dt = DateTime.Now.ToString("MM/dd/yyyy");

        if (!IsPostBack)
        {
            Install.PopulateDateFormat(ref ddlDateFormat);
            Install.PopulateDatabaseType(ref rbDatabsenam);
            Install.PopulateLanguages(ref ddlLang);
            rbDatabsenam.Items.FindByText("Oracle").Enabled = false;
            rbDatabsenam.Items.FindByText("DB2").Enabled = false;

        }


        try
        {
            string CnnStr = System.Configuration.ConfigurationManager.AppSettings["sqlConn"];

            string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

            //OleDbConnection cnn = new OleDbConnection(CnnStr);
            //if (cnn.State == ConnectionState.Closed)
            //{
            //    cnn.Open();
            //}

            if (server.Trim() == "None")
            {
                mvInstaller.ActiveViewIndex = 0;//0

            }
            else
            {

                lblAlrdyinstl.Text = "Application has been installed ";
                ModalPopupExtender2.Enabled = true;
                ModalPopupExtender2.Show();
                //mvInstaller.ActiveViewIndex = 6;

            }



            //Response.Redirect("~/login.aspx");

            //the folowing code has been used for blocking the instalation process if the application has been installed once and the below code in the catch block has to be pasted in here for showing the installation process right from the beginning

            //following code has been commented


            // lblAlrdyinstl.Text = "Application has already been installed ";
            // ModalPopupExtender2.Enabled = true;
            // ModalPopupExtender2.Show();

            //following code has been commented

            //the folowing code has been used for blocking the instalation process if the application has been installed once and the below code in the catch block has to be pasted in here for showing the installation process

            //following code has been added
            // mvInstaller.ActiveViewIndex = 0;
            //following code has been added
        }
        catch
        {
            //mvInstaller.ActiveViewIndex = 0;//0


        }


        if (hdn.Value.ToString() == "2")
        {
            mvInstaller.ActiveViewIndex = Convert.ToInt32(hdn.Value);

        }
        if (hdnconnfail.Value.ToString() == "3")
        {
            mvInstaller.ActiveViewIndex = Convert.ToInt32(hdnconnfail.Value);

        }

        if (hdnsamnam.Value.ToString() == "4")
        {
            mvInstaller.ActiveViewIndex = Convert.ToInt32(hdnsamnam.Value);

        }




    }
    protected void btnokinstl_Click(object sender, EventArgs e)
    {

        //Response.Redirect("~/Login");
        Response.Redirect("~/login.aspx");


    }

    protected void btnSaveLicense_Click(object sender, EventArgs e)
    {

        mvInstaller.ActiveViewIndex = 1;


    }
    protected void btnSaveNxt_Click(object sender, EventArgs e)
    {

        //INBL.Dateformat = ddlDateFormat.SelectedValue.ToString();
        //CalBeginDate.Format = INBL.Dateformat;
        //CalEndDate.Format = INBL.Dateformat;

        string Dateformat = ddlDateFormat.SelectedValue.ToString();
        CalBeginDate.Format = Dateformat;
        CalEndDate.Format = Dateformat;


        mvInstaller.ActiveViewIndex = 2;
        hdn.Value = "2";


    }
    protected void btnSavDatabase_Click(object sender, EventArgs e)
    {


        if (rbDatabsenam.SelectedValue == "msql")
        {
            mvInstaller.ActiveViewIndex = 3;
            txtPort.Text = System.Web.HttpContext.Current.Request.ServerVariables["SERVER_PORT"];
            hdnconnfail.Value = "3";
        }
        else
        {
            //lblmsg.Text = " MySql Database configuration is under development. ";
            //ModalPopupExtender1.Enabled = true;
            //ModalPopupExtender1.Show();
            mvInstaller.ActiveViewIndex = 3;
            txtPort.Text = System.Web.HttpContext.Current.Request.ServerVariables["SERVER_PORT"];
            hdnconnfail.Value = "3";

        }


    }
    protected void btnSavDatabseconn_Click(object sender, EventArgs e)
    {

        lblerror.Visible = false;
        if (TestConnection(txtServer.Text, txtDatabseusrnam.Text, txtDatabsepass.Text, txtDatabase.Text) == true)
        {
            System.Threading.Thread.Sleep(3000);
            mvInstaller.ActiveViewIndex = 4;
        }
        hdnfldDabaspass.Value = txtDatabsepass.Text.ToString();
       // INBL.DatabasePassword = hdnfldDabaspass.Value.ToString();
        string DatabasePassword = hdnfldDabaspass.Value.ToString();

    }

    protected void btnSaveAdmin_Click(object sender, EventArgs e)
    {

        hdnfldadminpass.Value = txtAdminPass.Text;

        mvInstaller.ActiveViewIndex = 5;
    }
    protected void btnSaveSchInform_Click(object sender, EventArgs e)
    {
        Install.AdminPassword = hdnfldadminpass.Value.ToString();
        Install.AdminUsername = txtAdminUsrnam.Text.ToString();
        Install.Language = ddlLang.SelectedValue.ToString();
        Install.Dateformat = ddlDateFormat.SelectedValue.ToString();
        Install.TimeZone = ddlTimeZon.SelectedValue.ToString();
        Install.DatabaseType = rbDatabsenam.SelectedValue.ToString();
        Install.Server = txtServer.Text.ToString().Trim();
        Install.Port = txtPort.Text.ToString().Trim();
        Install.Databasename = txtDatabase.Text.ToString().Trim();
        Install.DatabaseUsername = txtDatabseusrnam.Text.ToString().Trim();
        Install.DatabasePassword = hdnfldDabaspass.Value.ToString().Trim();
        Install.SchoolName = txtSchName.Text.ToString();
        Install.EndDate = txtEnddate.Text.ToString();
        Install.BeginDate = txtBegindate.Text.ToString();



        //string AdminPassword = hdnfldadminpass.Value.ToString();
        //string AdminUsername = txtAdminUsrnam.Text.ToString();
        //string Language = ddlLang.SelectedValue.ToString();
        //string Dateformat = ddlDateFormat.SelectedValue.ToString();
        //string TimeZone = ddlTimeZon.SelectedValue.ToString();
        //string DatabaseType = rbDatabsenam.SelectedValue.ToString();
        //string Server = txtServer.Text.ToString().Trim();
        //string Port = txtPort.Text.ToString().Trim();
        //string Databasename = txtDatabase.Text.ToString().Trim();
        //string DatabaseUsername = txtDatabseusrnam.Text.ToString().Trim();
        //string DatabasePassword = hdnfldDabaspass.Value.ToString().Trim();
        //string SchoolName = txtSchName.Text.ToString();
        //string EndDate = txtEnddate.Text.ToString();
        //string BeginDate = txtBegindate.Text.ToString();




        //    if (INBL.Dateformat == "yyyyMMdd")
        //    {
        //        DateTime.ParseExact(INBL.BeginDate, "yyyyMMdd",
        //DateTimeFormatInfo.InvariantInfo);
        //    }

       DateTime beginyr = Convert.ToDateTime(Install.BeginDate,DateTimeFormatInfo.InvariantInfo);
      //  DateTime beginyr = DateTime.Parse(Install.BeginDate);

        string beginyear = beginyr.Year.ToString();

        DateTime endyr = Convert.ToDateTime(Install.EndDate, DateTimeFormatInfo.InvariantInfo);

        string endyear = endyr.Year.ToString();

        Install.SchoolYearName = beginyear + "-" + endyear;




        //For creating database the following code has been used
        CheckAndCreateDatabase(Install.Server, Install.DatabaseUsername, Install.DatabasePassword, Install.Databasename);





    }

    protected void btnloginsis_Click(object sender, EventArgs e)
    {


    }
    protected void imgbtnlogin_Click(object sender, EventArgs e)
    {
        HttpCookie UserNameCookie = new HttpCookie("UserName");
        HttpCookie PasswordCookie = new HttpCookie("Password");
        HttpCookie LanguageCookie = new HttpCookie("Language");

        UserNameCookie.Values["UserName"] = null;
        PasswordCookie.Values["Password"] = null;
        LanguageCookie.Values["Language"] = null;
        Response.Cookies.Add(UserNameCookie);
        Response.Cookies.Add(PasswordCookie);
        Response.Cookies.Add(LanguageCookie);

        //Response.Redirect("~/Login");
        Response.Redirect("~/login.aspx");

    }



    protected void rbAgreement_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (rbAgreement.SelectedValue == "1")
        {
            btnExit.Enabled = true;
            btnSaveLicense.Enabled = false;
            btnExit.CssClass = "save_next";
            btnSaveLicense.CssClass = "save_next_inactive";

        }

        else
        {
            btnExit.Enabled = false;
            btnSaveLicense.Enabled = true;
            btnExit.CssClass = "save_next_inactive";
            btnSaveLicense.CssClass = "save_next";
        }


    }


    protected void btnExit_Click(object sender, EventArgs e)
    {

    }


    protected void btnBackSavDtaConn_Click(object sender, EventArgs e)
    {
        mvInstaller.ActiveViewIndex = 2;
    }
    //
    protected void btnbacksaveNext_Click(object sender, EventArgs e)
    {
        mvInstaller.ActiveViewIndex = 0;
    }
    protected void btnbckSavDatType_Click(object sender, EventArgs e)
    {
        mvInstaller.ActiveViewIndex = 1;
    }
    protected void btnbackSaveAdmin_Click(object sender, EventArgs e)
    {
        mvInstaller.ActiveViewIndex = 3;
    }
    protected void btnbackSaveSchInform_Click(object sender, EventArgs e)
    {
        mvInstaller.ActiveViewIndex = 4;
    }
    protected void btnbackLogin_Click(object sender, EventArgs e)
    {
        mvInstaller.ActiveViewIndex = 5;
        lblerror.Visible = false;
    }
    //



    private void CheckAndCreateDatabase(string servername, string userid, string password, string database)
    {
        if (TestConnection(servername, userid, password, database) == true)
        {
            if (rbDatabsenam.SelectedItem.Value == "msql")
            {
                Install.DatabaseType = "msql";

                if (!string.IsNullOrEmpty(servername) & !string.IsNullOrEmpty(database))
                {
                    Server oServer = new Server();
                    Microsoft.SqlServer.Management.Common.ServerConnection oServerConnection = new Microsoft.SqlServer.Management.Common.ServerConnection();

                    oServerConnection.ConnectionString = ConnStr;
                    oServerConnection.Connect();

                    oServer = new Server(oServerConnection);

                    foreach (Database db in oServer.Databases)
                    {
                        if (database == db.Name)
                        {
                            hdnsamnam.Value = "4";
                            mvInstaller.ActiveViewIndex = 4;
                            ModalPopupExtender1.Enabled = true;
                            ModalPopupExtender1.Show();
                            //lblsamedatanam.Visible = true;
                            //lblsamedatanam.Text = "The database already exist, try with another name";
                            lblmsg.Text = "The database already exist, try with another name";

                            break;
                        }
                        else
                        {
                            //the below code has been added just when the database creation is in restricted state otherwise should be commented
                            // mvInstaller.ActiveViewIndex = 6;

                        }

                    }

                    

                   // GradeLevelBL GLBL = new GradeLevelBL();
                  //  SchoolBL SCBL = new SchoolBL();
                  //  InstallationDL objDBCreate = new InstallationDL();
                  //  Installation objDBCreate = new Installation();
                    //commented to restrict database creation
                    OpenSISWCFServiceInstallationClient INBL = new OpenSISWCFServiceInstallationClient();

                    if (Install.CreateSQLDb(Install.Server, Install.Databasename, Install.DatabaseUsername, Install.DatabasePassword, Install.DatabaseType) == true)
                    {
                        ViewState.Add("ConStr", ConnStr);
                        string sqlpath = Server.MapPath("~/App_Data/SQLSCRIPT/SQLFile.sql");
                        //string sqlinsert = Server.MapPath("~/App_Data/InserScript.sql");


                        string StrCon = "";

                        if ((this.ViewState["ConStr"].ToString() != null))
                        {
                            StrCon = this.ViewState["ConStr"].ToString() + "Database=" + Install.Databasename + ";";
                            Install.Conn = StrCon;

                        }

                        if (Install.ExecuteSQLScript(sqlpath, StrCon, Install.DatabaseType))
                        {
                            INBL.InsertInstalsettings(Install.Language,Install.Dateformat,Install.DatabaseType,Install.Server,Install.Databasename,Install.DatabaseUsername,Install.DatabasePassword,Install.Port,Install.AdminUsername,Install.AdminPassword,Install.TimeZone);
                            Install.Schoolid = INBL.AddSchoolName(Install.SchoolName);
                           
                           // GLBL.School_Id = Install.Schoolid;
                            Install.Schoolyrid = INBL.InsertBeginEnddate(Install.BeginDate,Install.EndDate,Install.SchoolYearName);
                            INBL.InsertBeginEndDateintomarkin(Install.BeginDate,Install.EndDate,Install.Schoolid.ToString(),Install.Schoolyrid.ToString());
                            //********Update on 26/04/2010*************
                            ////********added on 10th july 09 for max grade
                            //Install.Gradename = "1";
                            //Install.Maxgrade = INBL.AddGradeLevelInstall(Install.Gradename,Install.Schoolid);

                            //INBL.UpdateMaxGradeInstall(Install.Maxgrade.ToString(),Install.Schoolid);

                            ////********added on 10th july 09 for min grade
                            //Install.Gradename = "0";
                            //Install.Mingrade = INBL.AddGradeLevelInstall(Install.Gradename, Install.Schoolid);

                            //INBL.UpdateMinGradeInstall(Install.Mingrade.ToString(), Install.Schoolid.ToString());
                            //************************************************
                            //added on 22nd september
                            INBL.InsertintoSchoolspeccatInstall(Install.Schoolid);

                            //*******************Ethnicity*******************
                            INBL.InsertintoSchoolSpecificEthnicity(Install.Schoolid);


                            //************************************************
                            mvInstaller.ActiveViewIndex = 6;


                            INBL.Close();
                        }
                        else
                        {


                        }

                    }
                    else
                    {

                    }


                    //commented to restrict database creation

                }
                else
                {


                }





            }
            //for mysql
            else
            {
                Install.DatabaseType = "mysql";
                OpenSISWCFServiceInstallationClient INBL = new OpenSISWCFServiceInstallationClient();


                if (!string.IsNullOrEmpty(servername) & !string.IsNullOrEmpty(database))
                {
                    DataTable dtmydb = new DataTable();
                    dtmydb = INBL.CheckMysqlDB(Install.Databasename,Install.Conn);


                    if (dtmydb.Rows.Count > 0)
                    {
                        hdnsamnam.Value = "4";
                        mvInstaller.ActiveViewIndex = 4;
                        ModalPopupExtender1.Enabled = true;
                        ModalPopupExtender1.Show();
                        lblmsg.Text = "The database already exist, try with another name";


                    }
                    else
                    {
                        //the below code has been added just when the database creation is in restricted state otherwise should be commented
                        // mvInstaller.ActiveViewIndex = 6;

                       // GradeLevelBL GLBL = new GradeLevelBL();
                       // SchoolBL SCBL = new SchoolBL();
                       // Installation objDBCreate = new Installation();

                        //commented to restrict database creation

                        if (Install.CreateSQLDb(Install.Server, Install.Databasename, Install.DatabaseUsername, Install.DatabasePassword, Install.DatabaseType) == true)
                        {
                            ViewState.Add("ConStr", ConnStr);
                            string sqlpath = Server.MapPath("~/App_Data/MYSQLSCRIPT/Createmysql.sql");
                            //string sqlinsert = Server.MapPath("~/App_Data/InserScript.sql");


                            string StrCon = "";

                            if ((this.ViewState["ConStr"].ToString() != null))
                            {
                                StrCon = this.ViewState["ConStr"].ToString() + "Database=" + Install.Databasename + ";Character Set=utf8;";
                                Install.Conn = StrCon;

                            }

                            if (Install.ExecuteSQLScript(sqlpath, StrCon, Install.DatabaseType))
                            {
                                INBL.InsertInstalsettings(Install.Language,Install.Dateformat,Install.DatabaseType,Install.Server,Install.Databasename,Install.DatabaseUsername,Install.DatabasePassword,Install.Port,Install.AdminUsername,Install.AdminPassword,Install.TimeZone);
                                Install.Schoolid = INBL.AddSchoolName(Install.SchoolName);
                               // GLBL.School_Id = INBL.Schoolid;
                                Install.Schoolyrid = INBL.InsertBeginEnddate(Install.BeginDate,Install.EndDate,Install.SchoolYearName);
                                INBL.InsertBeginEndDateintomarkin(Install.BeginDate,Install.EndDate,Install.Schoolid.ToString(),Install.Schoolyrid.ToString());

                                //********added on 10th july 09 for max grade
                                //Install.Gradename = "1";
                                //Install.Maxgrade = INBL.AddGradeLevelInstall(Install.Gradename, Install.Schoolid);

                                //INBL.UpdateMaxGradeInstall(Install.Mingrade.ToString(), Install.Schoolid);

                                ////********added on 10th july 09 for min grade
                                //Install.Gradename = "0";
                                //Install.Mingrade = INBL.AddGradeLevelInstall(Install.Gradename,Install.Schoolid);

                                //INBL.UpdateMinGradeInstall(Install.Mingrade.ToString(), Install.Schoolid.ToString());

                                //added on 22nd september
                                INBL.InsertintoSchoolspeccatInstall(Install.Schoolid);

                                mvInstaller.ActiveViewIndex = 6;
                            }
                            else
                            {


                            }

                        }
                        else
                        {

                        }

                    }


                    //commented to restrict database creation

                }
                else
                {


                }




            }


        }
    }

    private bool TestConnection(string servername, string userid, string password, string databasename)
    {

        try
        {

            if (rbDatabsenam.SelectedValue == "msql")
            {

                ConnStr = "Persist Security Info=False;User ID=" + userid + ";pwd=" + password + ";Data Source=" + servername + ";";
                //}

                string str = ConnStr;

                SqlConnection Conn = new SqlConnection();
                Conn.ConnectionString = ConnStr;

                if (Conn.State == ConnectionState.Closed)
                {
                    Conn.Open();
                    return true;
                }
                else
                {
                  //  InstallationDL dc = new InstallationDL();

                    return false;
                }

            }
            else
            {


                ConnStr = "uid=" + userid + ";pwd=" + password + ";Data Source=" + servername + ";";
                Install.Conn = ConnStr;
                string str = ConnStr;

                MySqlConnection Conn = new MySqlConnection();
                Conn.ConnectionString = ConnStr;

                if (Conn.State == ConnectionState.Closed)
                {
                    Conn.Open();
                    return true;
                }
                else
                {
                  //  InstallationDL dc = new InstallationDL();

                    return false;
                }



            }
        }

        catch
        {

            ModalPopupExtender1.Enabled = true;
            ModalPopupExtender1.Show();

            //lblerror.Visible = true;
            lblmsg.Text = "Connection failed for Server : " + txtServer.Text;
            mvInstaller.ActiveViewIndex = 3;
            return false;
        }



    }

    //protected void AddEthnicity(string Schoolid)
    //{
    //    DataTable dtethnicity = new DataTable();
    //    DataRow row;

    //    dtethnicity.Columns.Add("eth_id");
    //    dtethnicity.Columns.Add("eth_cd");
    //    dtethnicity.Columns.Add("eth_nm");
    //    dtethnicity.Columns.Add("last_updated");
    //    dtethnicity.Columns.Add("eth_flag");
    //    dtethnicity.Columns.Add("short_order");
    //    dtethnicity.Columns.Add("sch_id");
    //    row = dtethnicity.NewRow();
    //    row["eth_id"] = 1;
    //    row["eth_cd"] = "";
    //    row["eth_nm"] = "";
    //    row["last_updated"] = "";
    //    row["eth_flag"] = "";
    //    row["short_order"] = "";
    //    row["sch_id"] = Schoolid;
    //    dtethnicity.Rows.Add(row);

    //}

}
